BEGIN;

CREATE TABLE user (
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	first_name VARCHAR(25) NOT NULL,
	last_name VARCHAR(35) NOT NULL,
	login VARCHAR(15) NOT NULL,
	password VARCHAR(65) NOT NULL,
	email VARCHAR(30) NOT NULL,
	type VARCHAR(16) NOT NULL,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE district (
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	name VARCHAR(25) NOT NULL,
	description TEXT,
	user_id INT(11) NOT NULL,
	FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
	UNIQUE (sid),
	UNIQUE (name, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE school_type (
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	name VARCHAR(25) NOT NULL,
	description TEXT,
	user_id INT(11) NOT NULL,
	FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
	UNIQUE (sid),
	UNIQUE (name, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE school (
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	name VARCHAR(100) NOT NULL,
	type_id INT(11) NOT NULL,
	user_id INT(11) NOT NULL,
	district_id INT(11) NOT NULL,
	street VARCHAR(35) NOT NULL,
	zip VARCHAR(6) NOT NULL,
	city VARCHAR(30) NOT NULL,
	FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
	FOREIGN KEY (district_id) REFERENCES district(id) ON DELETE CASCADE,
	FOREIGN KEY (type_id) REFERENCES school_type(id) ON DELETE NO ACTION,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE contact_person (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	first_name VARCHAR(25) NOT NULL,
	last_name VARCHAR(35) NOT NULL,
	phone VARCHAR(9),
	email VARCHAR(35),
	school_id INT(11) NOT NULL,
	FOREIGN KEY (school_id) REFERENCES school(id) ON DELETE CASCADE,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE room (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	name VARCHAR(100) NOT NULL,
	street VARCHAR(35) NOT NULL,
	zip VARCHAR(6) NOT NULL,
	city VARCHAR(30) NOT NULL,
	capacity INT(11),
	lease_price DOUBLE,
	contact_first_name VARCHAR(25),
	contact_last_name VARCHAR(35),
	phone VARCHAR(9),
	mobile VARCHAR(9),
	email VARCHAR(35),
	tax_id VARCHAR(10),
	user_id INT(11) NOT NULL,
	column_count INT(11) NOT NULL,
	x_order VARCHAR(32),
	y_order VARCHAR(32),
	x_style VARCHAR(32),
	y_style VARCHAR(32),
	number_rules VARCHAR(32),
	row_count INT(11) NOT NULL,
	FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE room_place (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
  room_id INT(11) NOT NULL,
	row_number INT(11) NOT NULL,
	column_number INT(11) NOT NULL,
	label VARCHAR(12),
	type VARCHAR(30) NOT NULL,
	FOREIGN KEY (room_id) REFERENCES room(id) ON DELETE CASCADE,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE event (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_state VARCHAR(30) NOT NULL,
	sid VARCHAR(32) NOT NULL,
	name VARCHAR(50) NOT NULL,
	room_id INT(11) NOT NULL,
	date_time TIMESTAMP NOT NULL,
	description TEXT,
	price DOUBLE,
	FOREIGN KEY (room_id) REFERENCES room(id) ON DELETE CASCADE,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE row_header (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  object_state VARCHAR(30) NOT NULL,
  sid VARCHAR(32) NOT NULL,
  room_id INT(11) NOT NULL,
  row_number INT(11) NOT NULL,
  label VARCHAR(12),
  FOREIGN KEY (room_id) REFERENCES room(id) ON DELETE CASCADE,
  UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE reservation (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  object_state VARCHAR(30) NOT NULL,
  sid VARCHAR(32) NOT NULL,
  school_id INT(11) NOT NULL,
  event_id INT(11) NOT NULL,
  contact_person_id INT(11) NOT NULL,
  paid_place_amount INT(11) NOT NULL DEFAULT 0,
  free_place_amount INT(11) NOT NULL DEFAULT 0,
  creation_date TIMESTAMP NOT NULL,
  FOREIGN KEY (school_id) REFERENCES school(id) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES event(id) ON DELETE CASCADE,
  FOREIGN KEY (contact_person_id) REFERENCES contact_person(id) ON DELETE NO ACTION,
  UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE user ADD INDEX user_sid_idx (sid);
ALTER TABLE user ADD INDEX user_object_state_idx (object_state);

ALTER TABLE district ADD INDEX district_sid_idx (sid);
ALTER TABLE district ADD INDEX district_object_state_idx (object_state);
ALTER TABLE district ADD INDEX district_name_idx (name);
ALTER TABLE district ADD INDEX district_user_id_idx (user_id);

ALTER TABLE school_type ADD INDEX school_type_sid_idx (sid);
ALTER TABLE school_type ADD INDEX school_type_object_state_idx (object_state);
ALTER TABLE school_type ADD INDEX school_type_name_idx (name);
ALTER TABLE school_type ADD INDEX school_type_user_id_idx (user_id);

ALTER TABLE school ADD INDEX school_sid_idx (sid);
ALTER TABLE school ADD INDEX school_object_state_idx (object_state);
ALTER TABLE school ADD INDEX school_user_id_idx (user_id);
ALTER TABLE school ADD INDEX school_type_id_idx (type_id);

ALTER TABLE contact_person ADD INDEX contact_person_sid_idx (sid);
ALTER TABLE contact_person ADD INDEX contact_person_object_state_idx (object_state);
ALTER TABLE contact_person ADD INDEX contact_person_school_id_idx (school_id);

ALTER TABLE room ADD INDEX room_sid_idx (sid);
ALTER TABLE room ADD INDEX room_object_state_idx (object_state);
ALTER TABLE room ADD INDEX room_user_id_idx (user_id);

ALTER TABLE room_place ADD INDEX room_place_sid_idx (sid);
ALTER TABLE room_place ADD INDEX room_place_object_state_idx (object_state);
ALTER TABLE room_place ADD INDEX room_place_room_id_idx (room_id);

ALTER TABLE row_header ADD INDEX row_header_sid_idx (sid);
ALTER TABLE row_header ADD INDEX row_header_object_state_idx (object_state);
ALTER TABLE row_header ADD INDEX row_header_room_id_idx (room_id);

ALTER TABLE event ADD INDEX event_sid_idx (sid);
ALTER TABLE event ADD INDEX event_object_state_idx (object_state);
ALTER TABLE event ADD INDEX event_room_id_idx (room_id);

ALTER TABLE reservation ADD INDEX reservation_sid_idx (sid);
ALTER TABLE reservation ADD INDEX reservation_object_state_idx (object_state);
ALTER TABLE reservation ADD INDEX reservation_event_idx (event_id);
ALTER TABLE reservation ADD INDEX reservation_school_idx (school_id);

COMMIT;